package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;

import com.edu.dao.BaseDao;
import com.edu.dao.UserDao;
import com.edu.entity.Goods;
import com.edu.entity.User;
import com.edu.entity.address;

public class UserDaoImpl implements UserDao {
	BaseDao bd1 = new BaseDao();
	@Override
	public int sqlSign(User u) {
		// TODO Auto-generated method stub
		int i = 0;
		String sql = "INSERT INTO `user` (" + 
				"  `username`," + 
				"  `password`," + 
				"  `sex`," + 
				"  `phonenum`," + 
				"  `status`," + 
				"  `brithday`," + 
				"  `signdate`," + 
				"  `like`," + 
				"  `favorable_rate`," + 
				"  `address`," + 
				"  `fannum`," + 
				"  `follownum`," + 
				"  `buyertag`," + 
				"  `sellernum`," + 
				"  `introduce`," + 
				"  `headimg`" + 
				") VALUES (" + 
				"  ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?" + 
				");";
		Object[] obj = {u.getUsername(),u.getPassword(),u.getSex(),u.getPhonenum(),u.getStatus(),u.getBrithday(),u.getLike(),u.getFavorableRate(),u.getAddress(),u.getFannum(),u.getFollownum(),u.getBuyertag(),u.getSellernum(),u.getIntroduce(),u.getHeadimg()};
		return bd1.executeUpdate(sql, obj);
	}
	@Override
	public User sqlLogin(User u) {
		// TODO Auto-generated method stub
		Connection conn = bd1.getConn();
		StringBuffer sb = new StringBuffer();
		ArrayList<Object> objList = new ArrayList();
		if(u.getUsername() == null) {
			objList.add("%%");
		}else {
			objList.add(u.getUsername());
		}
		sb.append("SELECT * FROM user WHERE username = ?");
		int dataNum = 1;
		if(u.getPassword() != null) {
			sb.append(" AND password = ?");
			objList.add(u.getPassword());
		}
		if(u.getPhonenum() != null) {
			sb.append(" AND phonenum = ?");
			objList.add(u.getPhonenum());
		}
		if(u.getStatus() != 0) {
			sb.append(" AND status = ?");
			objList.add(u.getStatus());
		}
		if(u.getLike() != 0) {
			sb.append(" AND like = ?");
			objList.add(u.getLike());
		}
		if(u.getFavorableRate() != 0) {
			sb.append(" AND favorable_rate = ?");
			objList.add(u.getFavorableRate());
		}
		if(u.getAddress() != null) {
			sb.append(" AND address LIKE ?");
			objList.add("%"+u.getAddress()+"%");
		}
		if(u.getFannum() != 0) {
			sb.append(" AND fannum = ?");
			objList.add(u.getFannum());
		}
		if(u.getFollownum() != 0) {
			sb.append(" AND follownum = ?");
			objList.add(u.getFollownum());
		}
		if(u.getBuyertag() != 0) {
			sb.append(" AND buyertag = ?");
			objList.add(u.getBuyertag());
		}
		if(u.getSellernum() != 0) {
			sb.append(" AND sellernum = ?");
			objList.add(u.getSellernum());
		}
		if(u.getIntroduce() != null) {
			sb.append(" AND introduce = ?");
			objList.add(u.getIntroduce());
		}
		String sql = sb.toString();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<objList.size();i++) {
				ps.setObject(i+1, objList.get(i));
			}
			System.out.println(ps);
			rs = ps.executeQuery();
			User user = new User();
			while(rs.next()) {
				user.setUserid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setSex(rs.getInt(4));
				user.setPhonenum(rs.getString(5));
				user.setStatus(rs.getInt(6));
				user.setSigndate(rs.getDate(8));
				user.setBrithday(rs.getDate(7));
				user.setLike(rs.getInt(9));
				user.setFavorableRate(rs.getDouble(10));
				user.setAddress(rs.getString(11));
				user.setFannum(rs.getInt(12));
				user.setFollownum(rs.getInt(13));
				user.setBuyertag(rs.getInt(14));
				user.setSellernum(rs.getInt(15));
				user.setIntroduce(rs.getString(16));
				user.setHeadimg(rs.getString(17));
			}
			return user;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			bd1.closeAll(conn, ps, rs);
		}
		System.out.println(ps);
		
		return null;
	}
	@Override
	public ArrayList<address> sqlFindAddress(User u) {
		// TODO Auto-generated method stub
		ArrayList<address> list = new ArrayList();
		String sql = "select * from address where userid = ?";
		Connection conn = bd1.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, u.getUserid());
				rs = ps.executeQuery();
				while(rs.next()) {
					address address = new address();
					address.setAddressid(rs.getInt(1));
					address.setUserid(rs.getInt(2));
					address.setProvince(rs.getString(3));
					address.setCity(rs.getString(4));
					address.setSite(rs.getString(5));
					address.setOther(rs.getString(6));
					address.setDef(rs.getBoolean(7));
					address.setName(rs.getString(8));
					address.setPhoneNum(rs.getString(9));
					list.add(address);
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				bd1.closeAll(conn, ps, rs);
			}
		return null;
	}
	public address sqlFindAddress(int i) {
		// TODO Auto-generated method stub
		ArrayList<address> list = new ArrayList();
		String sql = "select * from address where addressid = ?";
		Connection conn = bd1.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, i);
				rs = ps.executeQuery();
				while(rs.next()) {
					address address = new address();
					address.setAddressid(rs.getInt(1));
					address.setUserid(rs.getInt(2));
					address.setProvince(rs.getString(3));
					address.setCity(rs.getString(4));
					address.setSite(rs.getString(5));
					address.setOther(rs.getString(6));
					address.setDef(rs.getBoolean(7));
					address.setName(rs.getString(8));
					address.setPhoneNum(rs.getString(9));
					return address;
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				bd1.closeAll(conn, ps, rs);
			}
		return null;
	}
	public int sqlFindGoodsNum(int i) {
		int num = 0;
		Connection conn = bd1.getConn();
		String sql = "select count(*) from goods where sellerid = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, i);
			System.out.println(ps);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				num = rs.getInt(1);
			}
			return num;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			bd1.closeAll(conn, ps, rs);
		}
		return 0;
	}
	public ArrayList<String> sqlFindUserTags(User u){
		ArrayList<String> list = new ArrayList();
		String sql = "SELECT tag FROM usertag where userid = ?";
		Connection conn = bd1.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, u.getUserid());
			System.out.println(ps);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				list.add(rs.getString(1));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			bd1.closeAll(conn, ps, rs);
		}
		return list;
	}

}
